# @Project: diablo
# @File: insert_dash_data
# @Time: 2023/8/22 14:07
# @User: Jolly
# @Email: jolly@007vin.com

"""
写入看板数据 - 定时每2小时
"""

import json
import copy
import pymysql
import time
from datetime import datetime
import threading
import traceback

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
from dbutils.pooled_db import PooledDB


lock = threading.Lock()


class InsertDashData:

    def __init__(self, runtime):

        start_time = time.time()

        # self.merchant_id = merchant_id
        self.runtime = runtime

        # 初始化数据库连接

        # 本地
        # self.diablo_conn_2_0 = self.get_mongo_connection("127.0.0.1", "27017")

        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 2.0测试库
        # self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.154", "27017")

        # 正式mysql
        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            # client = MongoClient(f"mongodb://{host}:{port}",
            #                      maxPoolSize=100,
            #                      minPoolSize=1,
            #                      maxIdleTimeMS=30000,
            #                      waitQueueTimeoutMS=2000)

            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=100,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        # 本地
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='127.0.0.1',
        #     user='root',
        #     port=3306,
        #     passwd='12345678',
        #     db='users',
        #     use_unicode=True)

        # 线上
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # 测试
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def date_time_to_timestamp(self, date_time_str):

        format_string = "%Y-%m-%d %H:%M:%S"

        # 将时间字符串解析为datetime对象
        datetime_obj = datetime.strptime(date_time_str, format_string)

        # 将datetime对象转换为时间戳（Unix时间戳，单位为秒）
        timestamp = datetime_obj.timestamp()

        return int(timestamp)

    def get_today_inquiry_data(self, merchant_id):
        """
        获取今日的询价单
        """
        connection = self.diablo_conn_2_0

        # 将日期时间对象格式化为字符串
        formatted_datetime = datetime.now().strftime('%Y-%m-%d') + " 00:00:00"
        # formatted_datetime = "2023-08-09 00:00:00"
        print(formatted_datetime)
        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "create_time": {
                        "$gt": formatted_datetime
                    }
                }
                docs = conn.find(filter_dict)

                if conn.count_documents(filter_dict) == 0:
                    return [], True

                data = [inquiry for inquiry in docs]

                return data, False

        except Exception as e:
            print("获取今日询价单数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def get_today_transfer_data(self, merchant_id):
        """
        获取今日的译码单
        """
        connection = self.diablo_conn_2_0

        # 将日期时间对象格式化为字符串
        formatted_datetime = datetime.now().strftime('%Y-%m-%d') + " 00:00:00"
        # formatted_datetime = "2023-08-09 00:00:00"
        print(formatted_datetime)
        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "transfer_info.transfer_time": {
                        "$gt": formatted_datetime
                    }
                }
                docs = conn.find(filter_dict)

                if conn.count_documents(filter_dict) == 0:
                    return [], True

                data = [inquiry for inquiry in docs]

                return data, False

        except Exception as e:
            print("获取今日询价单数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def get_new_customer_count(self, repair_shop_ids, merchant_id):
        """
        获取新客户数量
        """

        connection = self.diablo_conn_2_0

        # 将日期时间对象格式化为字符串
        formatted_datetime = datetime.now().strftime('%Y-%m-%d') + " 00:00:00"
        # formatted_datetime = "2023-08-09 00:00:00"

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["ddl_inquiry"]
            #
            #     filter_dict = {
            #         "merchant_id": merchant_id,
            #         "inquiry_user.repair_shop_id": repair_shop_id,
            #         "create_time": {
            #             "$lt": formatted_datetime
            #         }
            #     }
            #
            #     if conn.count_documents(filter_dict) > 0:
            #         return True
            #
            #     return False

                pipeline = [
                    {
                        "$match": {
                            "merchant_id": merchant_id,
                            "inquiry_user.repair_shop_id": {"$in": repair_shop_ids},
                            "create_time": {"$lt": formatted_datetime}
                        }
                    },
                    {
                        "$group": {
                            "_id": "$inquiry_user.repair_shop_id"
                        }
                    }
                ]

                result = conn.aggregate(pipeline)
                count = len(list(result))

                return count

        except Exception as e:
            print("获取今日询价单数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def get_band_data(self):
        """
        获取品牌中文
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 查询品牌数据，组装字典
            brand_dict = {}
            brand_sql = f"select c.brandCode, c.brandCn from cars_brand_config c "
            cursor.execute(brand_sql)
            brand_data = cursor.fetchall()

            for brand in brand_data:
                brand_dict[brand[0]] = brand[1]

            return brand_dict

        except Exception as e:
            print(f"获取品牌信息失败 {e}")
        finally:
            mysql_conn.close()

    def dash_merchant_main_data(self, inquiry_data, merchant_id):
        """
        商家订单主信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 计算主信息
            wait_quote = 0  # 待报价数
            wait_confirm = 0  # 已报价，待确认数
            order_num = 0  # 订单数
            repair_shop_ids = []
            for inquiry in inquiry_data:
                # inquiry = dict(inquiry)
                order_num += 1
                if inquiry.get("inquiry_status") == 3:
                    wait_quote += 1
                elif inquiry.get("inquiry_status") == 5:
                    wait_confirm += 1

                # 组装客户id
                repair_shop_id = inquiry.get("inquiry_user").get("repair_shop_id")
                repair_shop_ids.append(repair_shop_id)

            # 检查是否是新客户，就是看这条订单的客户有没有以前的订单，用字典存储，兼顾去重
            # customer_count = self.get_new_customer_count(repair_shop_ids)

            increase_customer_num = 0  # 新增客户数

            # 获取今日订单客户，去重
            repair_shop_ids = set(repair_shop_ids)

            total_customer_num = len(repair_shop_ids)

            key = ['merchant_id', 'wait_quote', 'wait_confirm', 'order_num', 'increase_customer', 'total_customer_num',
                   'runtime', 'datetime']

            key_param = [merchant_id, wait_quote, wait_confirm, order_num, increase_customer_num,
                         total_customer_num, self.runtime, datetime.now().strftime('%Y-%m-%d')]

            value = ""

            for i, v in enumerate(key):
                value += '"{}",'.format(key_param[i])

            values = ", ".join('{0}=values({0})'.format(k) for k in key)

            sql = '''insert into diablo_dash_merchant_main_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                ",".join(key), value[:-1], values)
            # print(sql)
            cursor.execute(sql)
            mysql_conn.commit()
        except Exception as e:
            print(f"写入商家订单主信息失败 {e}")
        finally:
            mysql_conn.close()

    def dash_user_main_data(self, inquiry_data, merchant_id):
        """
        商家员工订单主信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            increase_customer = {}  # 新客户数量

            user_ids = [inquiry.get("user_id") for inquiry in inquiry_data]

            user_ids = list(set(user_ids))  # 用户去重

            user_insert_data = []
            repair_shop_ids = []

            for index, user_id in enumerate(user_ids):

                wait_quote = 0  # 待报价数
                wait_confirm = 0  # 已报价，待确认数
                order_num = 0  # 订单数

                insert_data = {}

                for inquiry in inquiry_data:

                    # 只计算一遍新客户
                    if index == 0:
                        # 组装客户id
                        repair_shop_id = inquiry.get("inquiry_user").get("repair_shop_id")
                        repair_shop_ids.append(repair_shop_id)

                    if inquiry.get("user_id") == user_id:
                        # 计算订单数、待报价、待确认数量
                        order_num += 1
                        if inquiry.get("inquiry_status") == 3:
                            wait_quote += 1
                        elif inquiry.get("inquiry_status") == 5:
                            wait_confirm += 1

                # 检查是否是新客户，就是看这条订单的客户有没有以前的订单，用字典存储，兼顾去重
                # customer_count = self.get_new_customer_count(repair_shop_ids)

                increase_customer_num = 0  # 新增客户数

                # 获取今日订单客户，去重
                repair_shop_ids = set(repair_shop_ids)

                total_customer_num = len(repair_shop_ids)

                # 拼接数据
                insert_data["merchant_id"] = merchant_id
                insert_data["user_id"] = user_id
                insert_data["order_num"] = order_num
                insert_data["wait_quote"] = wait_quote
                insert_data["wait_confirm"] = wait_confirm
                insert_data["increase_customer"] = increase_customer_num
                insert_data["total_customer_num"] = total_customer_num
                insert_data["datetime"] = datetime.now().strftime('%Y-%m-%d')

                user_insert_data.append(insert_data)

            # 数据写入
            for user_data in user_insert_data:

                key = ['merchant_id', 'user_id', 'wait_quote', 'wait_confirm', 'order_num', 'increase_customer',
                       'total_customer_num', 'runtime', 'datetime']

                key_param = [merchant_id,
                             user_data.get("user_id", ""),
                             user_data.get("wait_quote", 0),
                             user_data.get("wait_confirm", 0),
                             user_data.get("order_num", 0),
                             user_data.get("increase_customer", 0),
                             user_data.get("total_customer_num", 0),
                             self.runtime,
                             datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_user_main_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入用户订单主信息失败 {e}")
        finally:
            mysql_conn.close()

    def dash_user_transfer_data(self, inquiry_data, merchant_id):
        """
        译码员译码数据信息
        """

        # 获取今天译码的单据
        inquiry_data, check = self.get_today_transfer_data(merchant_id)

        if check:
            return

        mysql_conn = self.mysql_conn.connection()

        vin_transfer_count = {}

        try:

            # 遍历所有询价单，获取译码单属于每个译码员的归属量，同一个vin码取最后一个单的译码员

            for inquiry in inquiry_data:
                if not inquiry.get("transfer_user"):
                    continue

                if inquiry.get("inquiry_status") < 3:
                    continue

                if not inquiry.get("transfer_info").get("transfer_time"):
                    continue

                if not inquiry.get("transfer_user").get("origin_transfer_user"):
                    continue

                transfer_user_id = inquiry.get("transfer_user")['origin_transfer_user'][0]['user_id']

                car_info = inquiry.get("car_info")
                vin = ""
                if car_info:
                    vin = car_info.get("vin")

                if not vin or vin == "":
                    continue

                vin_transfer_count[vin] = transfer_user_id

            value_count_dict = {}
            if vin_transfer_count:
                for key, value in vin_transfer_count.items():
                    if value in value_count_dict:
                        value_count_dict[value] += 1
                    else:
                        value_count_dict[value] = 1

            cursor = mysql_conn.cursor()

            user_datas = [{inquiry.get("transfer_user")['origin_transfer_user'][0]['user_id']:
                           inquiry.get("transfer_user")['origin_transfer_user'][0]['real_name']} for inquiry in
                          inquiry_data if inquiry.get("inquiry_status") > 2 and inquiry.get("transfer_user") is not None
                          and inquiry.get("transfer_user") != {} and inquiry.get("transfer_user").get('origin_transfer_user')]

            # 用户去重 使用集合来自动去重
            unique_user_datas = set(tuple(user_data.items()) for user_data in user_datas)

            # 将元组转换回字典形式，得到去重后的元素列表
            user_datas_ = [dict(user_tuple) for user_tuple in unique_user_datas]

            user_insert_data = []

            for user_data in user_datas_:

                transfer_num = 0  # 译码单数
                transfer_time_total = 0  # 译码总时长秒
                total_amount = 0  # 总金额，在环节5才进行计算

                transfer_time_list = []  # 译码时长列表

                insert_data = {}

                for inquiry in inquiry_data:
                    if not inquiry.get("transfer_user"):
                        continue

                    if inquiry.get("inquiry_status") < 3:
                        continue

                    if not inquiry.get("transfer_info").get("transfer_time"):
                        continue

                    if not inquiry.get("transfer_user").get("origin_transfer_user"):
                        continue

                    # 如果是隔天译码的

                    transfer_user_id = inquiry.get("transfer_user")['origin_transfer_user'][0]['user_id']

                    # if inquiry.get("transfer_user").get("origin_transfer_user"):
                    #     transfer_user_id = inquiry.get("transfer_user")['origin_transfer_user'][0]['user_id']
                    # else:
                    #     transfer_user_id = inquiry.get("transfer_user")['user_id']

                    if transfer_user_id == list(user_data.keys())[0]:
                        # 计算译码单数
                        transfer_num += 1

                        # 计算译码时长
                        create_time = inquiry.get("create_time")
                        # if not inquiry.get("transfer_info").get("transfer_time"):
                        #     transfer_time = inquiry.get("create_time")
                        # else:
                        #     transfer_time = inquiry.get("transfer_info")["transfer_time"]
                        transfer_time = inquiry.get("transfer_info")["transfer_time"]

                        # 去除创建时间和译码时间并转换成时间戳
                        create_time_timestamp = self.date_time_to_timestamp(create_time)
                        transfer_time_timestamp = self.date_time_to_timestamp(transfer_time)

                        # 得到差值，就是时长
                        transfer_time_long = transfer_time_timestamp - create_time_timestamp
                        transfer_time_list.append(transfer_time_long)

                        transfer_time_total += transfer_time_long  # 计算总时长

                        # 计算金额，只有是已报价的单据才进行计算

                        if inquiry.get("inquiry_status") > 3:

                            part_list = inquiry.get("part_list")
                            if part_list:
                                # 获取库存数据
                                for part in part_list:
                                    stock_list = part.get("quality_price")
                                    if stock_list:
                                        for stock in stock_list:
                                            # 获取库存售价
                                            quality_price = stock.get("quality_price", 0)
                                            total_amount += quality_price

                # 计算三个译码时长数据
                # 译码最长时间
                transfer_time_max = max(transfer_time_list)
                # 译码最短时间
                transfer_time_min = min(transfer_time_list)
                # 译码平均时间
                transfer_time_avg = 0 if len(transfer_time_list) == 0 else sum(transfer_time_list) / len(transfer_time_list)

                # 拼接插入数据
                insert_data["merchant_id"] = merchant_id
                insert_data["user_id"] = list(user_data.keys())[0]
                insert_data["user_name"] = list(user_data.values())[0]
                insert_data["transfer_num"] = transfer_num
                insert_data["transfer_time_total"] = transfer_time_total
                insert_data["transfer_time_avg"] = transfer_time_avg
                insert_data["transfer_time_max"] = transfer_time_max
                insert_data["transfer_time_min"] = transfer_time_min
                insert_data["total_amount"] = total_amount
                insert_data["datetime"] = datetime.now().strftime('%Y-%m-%d')
                insert_data["unique_transfer_num"] = value_count_dict.get(
                    list(user_data.keys())[0]) if value_count_dict.get(list(user_data.keys())[0]) else 0

                user_insert_data.append(insert_data)

            # 数据写入
            for user_data in user_insert_data:

                key = ['merchant_id', 'user_id', 'user_name', 'transfer_num', 'transfer_time_total', 'transfer_time_avg',
                       'transfer_time_max', 'transfer_time_min', 'total_amount', 'datetime', 'unique_transfer_num']

                key_param = [merchant_id,
                             user_data.get("user_id", ""),
                             user_data.get("user_name", ""),
                             user_data.get("transfer_num", 0),
                             user_data.get("transfer_time_total", 0),
                             user_data.get("transfer_time_avg", 0),
                             user_data.get("transfer_time_max", 0),
                             user_data.get("transfer_time_min", 0),
                             user_data.get("total_amount", 0),
                             datetime.now().strftime('%Y-%m-%d'),
                             user_data.get("unique_transfer_num", 0)]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_user_transfer_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入译码员译码数据信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_time_period_data(self, inquiry_data, merchant_id):
        """
        报价时段数据
        改，订单时段数据
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 定义24小时 {"0": 0, "1": 0 ......}
            time_seed = {str(i): 0 for i in range(24)}

            for inquiry in inquiry_data:

                # if inquiry.get("inquiry_status") < 5:
                #     continue

                # if not inquiry.get("quote_info"):
                #     # 如果当前是已报价，但是没有报价信息，说明是创建就直接报价了，时间取创建时间
                #     quote_time = inquiry.get("create_time")
                # else:
                #     # 正常报价
                #     quote_time = inquiry.get("quote_info")['quote_time']

                create_time = inquiry.get("create_time")

                # 判定创建时间所在时区

                # 将报价时间解析为datetime对象
                format_string = "%Y-%m-%d %H:%M:%S"
                datetime_obj = datetime.strptime(create_time, format_string)

                # 获取小时部分
                hour = datetime_obj.hour
                hour_str = str(hour)

                # 在时间段字典中对应的时间value上累加1
                if hour_str in time_seed:
                    time_seed[hour_str] += 1

            # 写入数据
            for hour, value in time_seed.items():

                key = ['merchant_id', 'hour', 'order_num', 'datetime']

                key_param = [merchant_id, hour, value, datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_time_period_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入报价时段数据信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_user_quote_data(self, inquiry_data, merchant_id):
        """
        报价员译码数据信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            user_datas = [{inquiry.get("platform_user")['uid']:
                           inquiry.get("platform_user")['username']} for inquiry in inquiry_data]

            # 用户去重 使用集合来自动去重
            unique_user_datas = set(tuple(user_data.items()) for user_data in user_datas)

            # 将元组转换回字典形式，得到去重后的元素列表
            user_datas_ = [dict(user_tuple) for user_tuple in unique_user_datas]

            user_insert_data = []

            for user_data in user_datas_:

                inquiry_num = 0  # 订单总数

                quote_time_total = 0  # 报价总时长秒
                wait_quote = 0
                wait_confirm = 0
                wait_send = 0
                deleted_order = 0
                total_amount = 0  # 总金额，在环节5才进行计算

                quote_time_list = []  # 报价时长列表

                insert_data = {}

                for inquiry in inquiry_data:
                    if not inquiry.get("platform_user"):
                        continue
                    platform_user_id = inquiry.get("platform_user")['uid']
                    if platform_user_id == list(user_data.keys())[0]:
                        # 计算订单单数
                        inquiry_num += 1

                        if inquiry.get("inquiry_status") == 3:  # 待报价
                            wait_quote += 1
                        elif inquiry.get("inquiry_status") == 5:  # 已报价待确认
                            wait_confirm += 1
                        elif inquiry.get("inquiry_status") == 6:  # 待发货
                            wait_send += 1
                        elif inquiry.get("inquiry_status") == 0:  # 已删除
                            deleted_order += 1
                        else:
                            continue  # 创建和待译码跳过，只计数

                        # 计算报价时长
                        if inquiry.get("inquiry_status") > 3:

                            if not inquiry.get("transfer_info"):
                                transfer_time = inquiry.get("create_time")
                            else:
                                transfer_time = inquiry.get("transfer_info")["transfer_time"]

                            if not inquiry.get("quote_info"):
                                quote_time = inquiry.get("create_time")
                            else:
                                quote_time = inquiry.get("quote_info")["quote_time"]

                            # 去除创建时间和译码时间并转换成时间戳
                            transfer_time_timestamp = self.date_time_to_timestamp(transfer_time)
                            quote_time_timestamp = self.date_time_to_timestamp(quote_time)

                            # 得到差值，就是时长
                            quote_time_long = quote_time_timestamp - transfer_time_timestamp
                            quote_time_list.append(quote_time_long)

                            quote_time_total += quote_time_long  # 计算总时长

                            # 计算金额，只有是已报价的单据才进行计算
                            part_list = inquiry.get("part_list")
                            if part_list:
                                # 获取库存数据
                                for part in part_list:
                                    stock_list = part.get("quality_price")
                                    if stock_list:
                                        for stock in stock_list:
                                            # 获取库存售价
                                            quality_price = stock.get("quality_price", 0)
                                            total_amount += quality_price

                # 报价平均时间
                quote_time_avg = 0 if len(quote_time_list) == 0 else sum(quote_time_list) / len(quote_time_list)

                # 拼接插入数据
                insert_data["merchant_id"] = merchant_id
                insert_data["user_id"] = list(user_data.keys())[0]
                insert_data["user_name"] = list(user_data.values())[0]
                insert_data["inquiry_num"] = inquiry_num
                insert_data["quote_time_total"] = quote_time_total
                insert_data["quote_time_avg"] = quote_time_avg
                insert_data["total_amount"] = total_amount
                insert_data["wait_quote"] = wait_quote
                insert_data["wait_confirm"] = wait_confirm
                insert_data["wait_send"] = wait_send
                insert_data["deleted_order"] = deleted_order
                insert_data["datetime"] = datetime.now().strftime('%Y-%m-%d')

                user_insert_data.append(insert_data)

            # 数据写入
            for user_data in user_insert_data:

                key = ['merchant_id', 'user_id', 'user_name', 'inquiry_num', 'quote_time_total', 'quote_time_avg',
                       'total_amount', 'wait_quote', 'wait_confirm', 'wait_send', 'deleted_order', 'datetime']

                key_param = [merchant_id,
                             user_data.get("user_id", ""),
                             user_data.get("user_name", ""),
                             user_data.get("inquiry_num", 0),
                             user_data.get("quote_time_total", 0),
                             user_data.get("quote_time_avg", 0),
                             user_data.get("total_amount", 0),
                             user_data.get("wait_quote", 0),
                             user_data.get("wait_confirm", 0),
                             user_data.get("wait_send", 0),
                             user_data.get("deleted_order", 0),
                             datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_user_inquiry_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入报价员译码数据信息信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_active_data(self, inquiry_data, merchant_id):
        """
        活跃数据统计，每小时用户数（去重），每小时报价金额数
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 定义24小时 {"0": 0, "1": 0 ......}
            time_seed = {str(i): [] for i in range(24)}

            # 小时金额
            time_amount_seed = {str(i): 0 for i in range(24)}

            for inquiry in inquiry_data:

                create_time = inquiry.get("create_time")

                # 判定时间所在时区
                # 将报价时间解析为datetime对象
                format_string = "%Y-%m-%d %H:%M:%S"
                datetime_obj = datetime.strptime(create_time, format_string)

                # 获取小时部分
                hour = datetime_obj.hour
                hour_str = str(hour)

                # 在时间段字典中对应的时间value列表，添加客户id
                if hour_str in time_seed:
                    time_seed[hour_str].append(inquiry.get("inquiry_user").get("repair_shop_id"))

                # 计算金额
                if inquiry.get("inquiry_status") > 3:
                    part_list = inquiry.get("part_list")
                    if part_list:
                        # 获取库存数据
                        for part in part_list:
                            stock_list = part.get("quality_price")
                            if stock_list:
                                for stock in stock_list:
                                    # 获取库存售价
                                    quality_price = stock.get("quality_price", 0)

                                    # 累加金额
                                    time_amount_seed[hour_str] = copy.deepcopy(time_amount_seed).get(hour_str) + quality_price

            # 写入数据
            for hour, value in time_seed.items():

                key = ['merchant_id', 'hour', 'order_num', 'total_amount', 'datetime']

                key_param = [merchant_id, hour, len(set(value)), time_amount_seed.get(hour, 0), datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_active_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入活跃数据统计信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_customer_active_data(self, inquiry_data, merchant_id):
        """
        活跃数据统计，客户订单数，金额
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 客户信息
            customer_name_data = {}  # 客户id-姓名
            customer_id_amount_data = {}  # 客户id-金额
            customer_id_order_data = {}  # 客户id-订单数

            for inquiry in inquiry_data:

                # 客户信息
                inquiry_user = inquiry.get("inquiry_user")

                if not inquiry_user:
                    continue

                customer_name = inquiry_user.get("name")
                repair_shop_id = inquiry_user.get("repair_shop_id")

                customer_name_data[repair_shop_id] = customer_name

                # 累加订单
                if not customer_id_order_data.get(repair_shop_id):
                    customer_id_order_data[repair_shop_id] = 1
                else:
                    customer_id_order_data[repair_shop_id] = copy.deepcopy(
                        customer_id_order_data).get(repair_shop_id) + 1

                # 计算金额
                if inquiry.get("inquiry_status") > 3:
                    part_list = inquiry.get("part_list")
                    if part_list:
                        # 获取库存数据
                        for part in part_list:
                            stock_list = part.get("quality_price")
                            if stock_list:
                                for stock in stock_list:
                                    # 获取库存售价
                                    quality_price = stock.get("quality_price", 0)

                                    # 累加金额
                                    if not customer_id_amount_data.get(repair_shop_id):
                                        customer_id_amount_data[repair_shop_id] = quality_price
                                    else:
                                        customer_id_amount_data[repair_shop_id] = copy.deepcopy(
                                            customer_id_amount_data).get(repair_shop_id) + quality_price

            # 写入数据
            for customer_id, amount in customer_id_amount_data.items():

                key = ['merchant_id', 'customer_id', 'customer', 'order_num', 'total_amount', 'datetime']

                key_param = [merchant_id, customer_id, customer_name_data.get(customer_id, ""),
                             customer_id_order_data.get(customer_id, ""), customer_id_amount_data.get(customer_id, ""),
                             datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_customer_active_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入活跃数据统计信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_brand_data(self, inquiry_data, merchant_id):
        """
        品牌数量统计
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            brand_data = {}
            brand_logo_map = {}

            # 查询品牌
            brand_dict = self.get_band_data()

            for inquiry in inquiry_data:

                car_info = inquiry.get("car_info")

                if not car_info:
                    continue

                brand_code = car_info.get("brandCode")
                brand_logo = car_info.get("brand_logo")

                if brand_code and brand_logo:

                    # 写入品牌logo映射
                    brand_logo_map[brand_code] = brand_logo

                    # 写入品牌字典计数
                    if brand_data.get(brand_code):
                        brand_data[brand_code] = copy.deepcopy(brand_data).get(brand_code) + 1
                    else:
                        brand_data[brand_code] = 1

            # 写入数据
            for brand, num in brand_data.items():

                key = ['merchant_id', 'brand_name', 'brand_logo', 'brand_num', 'datetime']

                key_param = [merchant_id, brand_dict.get(brand, brand), brand_logo_map.get(brand, ""), num,
                             datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_brand_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入品牌数量统计信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_quality_data(self, inquiry_data, merchant_id):
        """
        品质数量统计
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            quality_data = {}

            for inquiry in inquiry_data:

                # 获取零件
                part_list = inquiry.get("part_list")

                if not part_list:
                    continue

                for part in part_list:

                    # 获取库存
                    stock_list = part.get("quality_price")

                    if not stock_list:
                        continue

                    for stock in stock_list:
                        # 获取品质名
                        quality_name = stock.get("quality_name")

                        if quality_name:

                            quality_name = quality_name.split(")")[1] if ")" in quality_name else quality_name

                            if quality_data.get(quality_name):
                                quality_data[quality_name] = copy.deepcopy(quality_data).get(quality_name) + 1
                            else:
                                quality_data[quality_name] = 1

            # 写入数据
            for quality, num in quality_data.items():

                key = ['merchant_id', 'quality_name', 'quality_num', 'datetime']

                key_param = [merchant_id, quality, num, datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_quality_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入品质数量统计信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def dash_oe_data(self, inquiry_data, merchant_id):
        """
        零件数量统计
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            oe_data = {}
            oe_name_map = {}

            for inquiry in inquiry_data:

                part_list = inquiry.get("part_list")

                if not part_list:
                    continue

                for part in part_list:

                    # 获取库存数据
                    stock_list = part.get("quality_price")

                    oe = part.get("oe")
                    part_name = part.get("part_name")

                    if not stock_list:
                        continue

                    for stock in stock_list:
                        # 获取品质名
                        quality_name = stock.get("quality_name")

                        if quality_name:

                            quality_name = quality_name.split(")")[1] if ")" in quality_name else quality_name

                            # 拼接oe名 + 品质
                            oe_name_quality = part_name + "@" + quality_name

                            if oe and part_name:

                                # 写入oe名称映射    oe名 - 品质 - oe号
                                oe_name_quality = oe_name_quality + "@" + oe

                                # 校验拼接数据是否完整
                                if len(oe_name_quality.split("@")) != 3:
                                    continue

                                # 写入oe字典计数
                                if oe_data.get(oe_name_quality):
                                    oe_data[oe_name_quality] = copy.deepcopy(oe_data).get(oe_name_quality) + 1
                                else:
                                    oe_data[oe_name_quality] = 1

            # 写入数据
            for oe, num in oe_data.items():

                oe_ = oe.split("@")
                oe_name = oe_[0] + '-' + oe_[1]  # oe名加品质名
                oe = oe_[2]  # oe号

                key = ['merchant_id', 'oe_name', 'oe', 'oe_num', 'datetime']

                key_param = [merchant_id, oe_name, oe, num, datetime.now().strftime('%Y-%m-%d')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                sql = '''insert into diablo_dash_oe_data ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                    ",".join(key), value[:-1], values)
                # print(sql)
                cursor.execute(sql)
                mysql_conn.commit()

        except Exception as e:
            print(f"写入零件数量统计信息失败 {e}")
            print(traceback.format_exc())
        finally:
            mysql_conn.close()

    def get_merchant_data(self):
        """
        获取所有商家信息，然后遍历写入看板数据
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # 查询商家id，加入列表返回
            merchant_ids = []
            merchant_sql = f"select DISTINCT(merchant_id) merchant_id from diablo_merchant "
            cursor.execute(merchant_sql)
            merchant_data = cursor.fetchall()

            for merchant in merchant_data:
                merchant_ids.append(merchant[0])

            return merchant_ids

        except Exception as e:
            print(f"获取商家信息失败 {e}")
        finally:
            mysql_conn.close()

    # def dash_user_transfer_data_delay_fix(self, merchant_id):
    #     """
    #     矫正过去7天的译码单，因为译码元可能会隔天甚至隔很多天译码，导致每天计算的译码单数量会抛弃那些隔天的单
    #     """
    #
    #     from datetime import datetime, timedelta
    #
    #     connection = self.diablo_conn_2_0
    #
    #     # 获取今天的日期
    #     today = datetime.today()
    #
    #     # 格式化日期成 "2023-08-09 00:00:00" 格式
    #     formatted_today = today.strftime("%Y-%m-%d 00:00:00")
    #     print("今天的日期:", formatted_today)
    #
    #     # 获取前7天的日期
    #     seven_days_ago = today - timedelta(days=7)
    #
    #     all_inquiry_data_ = []
    #
    #     # 获取这段时间内的所有译码员
    #     try:
    #         if connection is not None:
    #
    #             current_date = seven_days_ago + timedelta(days=0)
    #             formatted_date = current_date.strftime("%Y-%m-%d 00:00:00")
    #
    #             end_date = formatted_date.split(" ")[0] + " 23:59:59"
    #
    #             # 执行数据库操作
    #             db = connection["ddll"]
    #             conn = db["ddl_inquiry"]
    #
    #             filter_dict = {
    #                 "merchant_id": merchant_id,
    #                 "create_time": {
    #                     "$gt": formatted_date,
    #                     "$lt": end_date
    #                 }
    #             }
    #             docs = conn.find(filter_dict)
    #
    #             if conn.count_documents(filter_dict) == 0:
    #                 return
    #
    #             all_inquiry_data_ = [inquiry for inquiry in docs]
    #
    #     except Exception as e:
    #         print("获取今日询价单数据失败 - mongo：", e)
    #     finally:
    #         # 将连接放回连接池
    #         connection.close()
    #
    #     user_datas = [{inquiry.get("transfer_user")['origin_transfer_user'][0]['user_id']:
    #                        inquiry.get("transfer_user")['origin_transfer_user'][0]['real_name']} for inquiry in
    #                   all_inquiry_data_ if inquiry.get("inquiry_status") > 2 and inquiry.get("transfer_user") is not None]
    #
    #     # 用户去重 使用集合来自动去重
    #     unique_user_datas = set(tuple(user_data.items()) for user_data in user_datas)
    #
    #     # 将元组转换回字典形式，得到去重后的元素列表
    #     user_datas_ = [dict(user_tuple) for user_tuple in unique_user_datas]
    #
    #     # 遍历7天
    #     for i in range(7):
    #         current_date = seven_days_ago + timedelta(days=i)
    #         formatted_date = current_date.strftime("%Y-%m-%d 00:00:00")
    #         print("前{}天的日期:".format(i), formatted_date)
    #
    #         end_date = formatted_date.split(" ")[0] + " 23:59:59"
    #
    #         inquiry_data_ = []
    #         # 获取这天的询价单
    #         try:
    #             if connection is not None:
    #                 # 执行数据库操作
    #                 db = connection["ddll"]
    #                 conn = db["ddl_inquiry"]
    #
    #                 filter_dict = {
    #                     "merchant_id": merchant_id,
    #                     "create_time": {
    #                         "$gt": formatted_date,
    #                         "$lt": end_date
    #                     }
    #                 }
    #                 docs = conn.find(filter_dict)
    #
    #                 if conn.count_documents(filter_dict) == 0:
    #                     return
    #
    #                 inquiry_data_ = [inquiry for inquiry in docs]
    #
    #         except Exception as e:
    #             print("获取今日询价单数据失败 - mongo：", e)
    #         finally:
    #             # 将连接放回连接池
    #             connection.close()
    #
    #         # 遍历这天的询价单，统计不是当天译码的数量，以及指明是哪天译码的
    #         for inquiry in inquiry_data_:
    #
    #             if not inquiry.get("transfer_user"):
    #                 continue
    #
    #             if inquiry.get("inquiry_status") < 2:
    #                 continue
    #
    #             if not inquiry.get("transfer_info").get("transfer_time"):
    #                 continue
    #
    #             if not inquiry.get("transfer_user").get("origin_transfer_user"):
    #                 continue
    #
    #             # 判断是否非当天译码的


if __name__ == "__main__":
    # 获取当前写入时间
    runtime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(runtime)
    service = InsertDashData(runtime=runtime)

    # 获取商家id列表
    merchant_ids = service.get_merchant_data()
    if len(merchant_ids) == 0:
        print("未查询到商家id信息")

    for merchant_id in merchant_ids:

        inquiry_data, check = service.get_today_inquiry_data(merchant_id)
        if check:
            print(f"商家id{merchant_id}今日无询价单数据")
        else:
            # 商家订单主信息 - diablo_dash_merchant_main_data
            service.dash_merchant_main_data(inquiry_data, merchant_id)

            # 商家员工订单主信息 - diablo_dash_user_main_data
            service.dash_user_main_data(inquiry_data, merchant_id)

            # 译码员译码数据信息 - diablo_dash_user_transfer_data
            service.dash_user_transfer_data(inquiry_data, merchant_id)

            # 报价时段数据 - diablo_dash_time_period_data
            service.dash_time_period_data(inquiry_data, merchant_id)

            # 报价员译码数据信息 - diablo_dash_user_quote_data
            service.dash_user_quote_data(inquiry_data, merchant_id)

            # 活跃数据统计，每小时订单创建数，每小时报价金额数 - diablo_dash_active_data
            service.dash_active_data(inquiry_data, merchant_id)

            # 活跃数据统计，客户订单数，金额 - diablo_dash_customer_active_data
            service.dash_customer_active_data(inquiry_data, merchant_id)

            # 品牌数量统计 - diablo_dash_brand_data
            service.dash_brand_data(inquiry_data, merchant_id)

            # 品质数量统计 - diablo_dash_quality_data
            service.dash_quality_data(inquiry_data, merchant_id)

            # 零件数量统计 - diablo_dash_oe_data
            service.dash_oe_data(inquiry_data, merchant_id)

            # ----------------------------
            # 矫正过去5天的译码单，因为译码元可能会隔天甚至隔很多天译码，导致每天计算的译码单数量会抛弃那些隔天的单
            # service.dash_user_transfer_data_delay_fix(merchant_id)